﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace SisCRA.Database
{
    public class StudentEnrollmentDAL
    {
        #region -Constants-

        private const string TABLE = "ALUNO-DISCIPLINA";
        private const string VIEW = "V-ALUNO-DISCIPLINA";
        private const string INSERT_STUDENT_DISCIPLINE = "INSERT INTO T_ALUNO_DISCIPLINA (FK_ALUNO_ID, FK_DISCIPLINA_ID, DN_FALTAS, DN_MEDIA_FINAL, DC_SITUACAO) VALUES (@ALUNO_ID, @DISCIPLINA_ID, @FALTAS, @MEDIA_FINAL, @SITUACAO)";
        private const string DELETE_STUDENT_DISCIPLINE = "DELETE FROM T_ALUNO_DISCIPLINA WHERE FK_ALUNO_ID = @ALUNO_ID AND FK_DISCIPLINA_ID = @DISCIPLINA_ID";
        private const string UPDATE_STUDENT_FAULTS = "UPDATE T_ALUNO_DISCIPLINA SET DN_FALTAS = @FALTAS WHERE FK_ALUNO_ID = @ALUNO_ID AND FK_DISCIPLINA_ID = @DISCIPLINA_ID";
        private const string UPDATE_STUDENT_FINAL_GRADE_AND_SITUATION = "UPDATE T_ALUNO_DISCIPLINA SET DN_MEDIA_FINAL = @MEDIA_FINAL, DC_SITUACAO = @SITUACAO WHERE FK_ALUNO_ID = @ALUNO_ID AND FK_DISCIPLINA_ID = @DISCIPLINA_ID";
        private const string GET_STUDENT_ENROLL = "SELECT FK_ALUNO_ID AS ALUNO_ID, FK_DISCIPLINA_ID AS DISCIPLINA_ID, DN_FALTAS AS FALTAS, DN_MEDIA_FINAL AS MEDIA_FINAL, DC_SITUACAO AS SITUACAO FROM T_ALUNO_DISCIPLINA WHERE FK_ALUNO_ID = @ALUNO_ID AND FK_DISCIPLINA_ID = @DISCIPLINA_ID";
        private const string GET_STUDENT_NOT_ENROLL = "SELECT PK_ALUNO_ID AS ID, DC_PRONTUARIO AS PRONTUARIO, DC_NOME AS NOME, DC_EMAIL AS EMAIL FROM T_ALUNO WHERE PK_ALUNO_ID NOT IN (SELECT FK_ALUNO_ID FROM T_ALUNO_DISCIPLINA WHERE FK_DISCIPLINA_ID = @DISCIPLINA_ID) ORDER BY NOME";
        private const string GET_ALL_STUDENT_BY_DISCIPLINE = "SELECT A.PK_ALUNO_ID AS ID, A.DC_PRONTUARIO AS PRONTUARIO, A.DC_NOME AS NOME, A.DC_EMAIL AS EMAIL FROM T_ALUNO A, T_DISCIPLINA D, T_ALUNO_DISCIPLINA AD WHERE A.PK_ALUNO_ID = AD.FK_ALUNO_ID AND D.PK_DISCIPLINA_ID = AD.FK_DISCIPLINA_ID AND D.PK_DISCIPLINA_ID = @DISCIPLINA_ID ORDER BY NOME";
        private const string GET_ENROLLMENT_BY_DISCIPLINE = "SELECT * FROM V_ALUNO_DISCIPLINA WHERE DISCIPLINA_ID = @DISCIPLINA_ID";

        #endregion

        #region -Private Attributes-

        MySqlConnection conn;
        MySqlCommand cmd;
        MySqlDataAdapter da;

        #endregion

        #region -Public Methods-

        /// <summary>
        /// Método que insere uma relação Aluno-Disciplina no banco de dados.
        /// </summary>
        /// <param name="studentId">Id do aluno</param>
        /// <param name="disciplineId">Id da disciplina</param>
        public void InsertStudentDiscipline(int studentId, int disciplineId)
        {
            try
            {
                using (conn = Connection.Instance.Conn)
                {
                    conn.Open();
                    using (cmd = new MySqlCommand(INSERT_STUDENT_DISCIPLINE, conn))
                    {
                        cmd.Parameters.AddWithValue("@ALUNO_ID", studentId);
                        cmd.Parameters.AddWithValue("@DISCIPLINA_ID", disciplineId);
                        cmd.Parameters.AddWithValue("@FALTAS", 0);
                        cmd.Parameters.AddWithValue("@MEDIA_FINAL", null);
                        cmd.Parameters.AddWithValue("@SITUACAO", "CURSANDO");

                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Método que remove uma relação Aluno-Disciplina do banco de dados.
        /// </summary>
        /// <param name="studentId">Id do aluno</param>
        /// <param name="disciplineId">Id da disciplina</param>
        public void DeleteStudentDiscipline(int studentId, int disciplineId)
        {
            try
            {
                using (conn = Connection.Instance.Conn)
                {
                    conn.Open();
                    using (cmd = new MySqlCommand(DELETE_STUDENT_DISCIPLINE, conn))
                    {
                        cmd.Parameters.AddWithValue("@ALUNO_ID", studentId);
                        cmd.Parameters.AddWithValue("@DISCIPLINA_ID", disciplineId);

                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Método que atualiza a quantidade de faltas de um aluno em uma disciplina.
        /// </summary>
        /// <param name="studentId">Id do aluno</param>
        /// <param name="disciplineId">Id da disciplina</param>
        /// <param name="faults">Número de faltas</param>
        public void UpdateStudentFaults(int studentId, int disciplineId, int faults)
        {
            try
            {
                using (conn = Connection.Instance.Conn)
                {
                    conn.Open();
                    using (cmd = new MySqlCommand(UPDATE_STUDENT_FAULTS, conn))
                    {
                        cmd.Parameters.AddWithValue("@FALTAS", faults);
                        cmd.Parameters.AddWithValue("@ALUNO_ID", studentId);
                        cmd.Parameters.AddWithValue("@DISCIPLINA_ID", disciplineId);

                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Método que atualiza a média final e a situação de um aluno em uma disciplina.
        /// </summary>
        /// <param name="studentId">Id do aluno</param>
        /// <param name="disciplineId">Id da disciplina</param>
        /// <param name="finalGrade">Média final</param>
        /// <param name="situation">Situação do aluno</param>
        public void UpdateStudentFinalGradeAndSituation(int studentId, int disciplineId, double finalGrade, string situation)
        {
            try
            {
                using (conn = Connection.Instance.Conn)
                {
                    conn.Open();
                    using (cmd = new MySqlCommand(UPDATE_STUDENT_FINAL_GRADE_AND_SITUATION, conn))
                    {
                        cmd.Parameters.AddWithValue("@MEDIA_FINAL", finalGrade);
                        cmd.Parameters.AddWithValue("@SITUACAO", situation);
                        cmd.Parameters.AddWithValue("@ALUNO_ID", studentId);
                        cmd.Parameters.AddWithValue("@DISCIPLINA_ID", disciplineId);

                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Método que busca um aluno matriculado em uma disciplina.
        /// </summary>
        /// <param name="studentId">Id do aluno</param>
        /// <param name="disciplineId">Id da disciplina</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetStudentEnroll(int studentId, int disciplineId)
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                using (conn = Connection.Instance.Conn)
                {
                    conn.Open();
                    using (cmd = new MySqlCommand(GET_STUDENT_ENROLL, conn))
                    {
                        cmd.Parameters.AddWithValue("@ALUNO_ID", studentId);
                        cmd.Parameters.AddWithValue("@DISCIPLINA_ID", disciplineId);
                        using (da = new MySqlDataAdapter())
                        {
                            da.SelectCommand = cmd;
                            da.Fill(dt);
                        }
                    }
                }

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Método que busca todos os alunos não matriculados na disciplina.
        /// </summary>
        /// <param name="disciplineId">Id da disciplina</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetStudentNotEnroll(int disciplineId)
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                using (conn = Connection.Instance.Conn)
                {
                    conn.Open();
                    using (cmd = new MySqlCommand(GET_STUDENT_NOT_ENROLL, conn))
                    {
                        cmd.Parameters.AddWithValue("@DISCIPLINA_ID", disciplineId);
                        using (da = new MySqlDataAdapter())
                        {
                            da.SelectCommand = cmd;
                            da.Fill(dt);
                        }
                    }
                }

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Método que busca todos os alunos de uma disciplina.
        /// </summary>
        /// <param name="disciplineId">Id da disciplina</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetStudentByDiscipline(int disciplineId)
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                using (conn = Connection.Instance.Conn)
                {
                    conn.Open();
                    using (cmd = new MySqlCommand(GET_ALL_STUDENT_BY_DISCIPLINE, conn))
                    {
                        cmd.Parameters.AddWithValue("@DISCIPLINA_ID", disciplineId);
                        using (da = new MySqlDataAdapter())
                        {
                            da.SelectCommand = cmd;
                            da.Fill(dt);
                        }
                    }
                }

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Método que busca os dados final de uma disciplina.
        /// </summary>
        /// <param name="disciplineId">Id da disciplina</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetEnrollmentByDiscipline(int disciplineId)
        {
            DataTable dt = new DataTable(VIEW);

            try
            {
                using (conn = Connection.Instance.Conn)
                {
                    conn.Open();
                    using (cmd = new MySqlCommand(GET_ENROLLMENT_BY_DISCIPLINE, conn))
                    {
                        cmd.Parameters.AddWithValue("@DISCIPLINA_ID", disciplineId);
                        using (da = new MySqlDataAdapter())
                        {
                            da.SelectCommand = cmd;
                            da.Fill(dt);
                        }
                    }
                }

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
